# -*- coding: utf-8 -*-
# @Time : 2021/4/21 20:56
# @Author : lucy
# @Email : lilu7@asiainfo.com
# @File : 001.py
# @Project : basic_gramer


import pymysql


def connection():
    db = pymysql.connect(
        host="49.233.39.160",
        port=3306,
        database="lebo16",
        user="user",
        password="leboAa!#$123"
    )
    return db


def create_table():
    db = connection()
    cursor = db.cursor()
    cursor.execute('drop table if EXISTS lucy')
    sql = """
    create table lucy(
    stu_no int primary key auto_increment,
    name varchar(10),
    age int
    )
    """
    try:
        cursor.execute(sql)
        print('成功')
    except Exception as e:
        print('失败', e)
    finally:
        cursor.close()


def insert_data():
    # db = pymysql.connect('49.233.39.160', 'user', 'leboAa!#$123', 'lebo16', 3306)
    db = connection()
    cursor = db.cursor()
    sql = """
    insert into lucy(name,age) values('lucy',25)
    """
    try:
        cursor.execute(sql)
        db.commit()
        print('插入成功')
    except Exception as e:
        print('插入失败', e)
        db.rollback()
    finally:
        cursor.close()


def update_data():
    # db = pymysql.connect('49.233.39.160', 'user', 'leboAa!#$123', 'lebo16', 3306)
    db = connection()
    cursor = db.cursor()
    sql = """
    update lucy set name = 'lilu' where name = 'lucy';
    """
    try:
        cursor.execute(sql)
        db.commit()
        print('修改成功')
    except Exception as e:
        db.rollback()
        print('修改失败', e)
    finally:
        db.close()


def delete_date():
    db = connection()
    cursor = db.cursor()
    sql = """
    delete from lucy where name = 'lilu';
       """
    try:
        cursor.execute(sql)
        db.commit()
        print('删除成功')
    except Exception as e:
        db.rollback()
        print('删除失败', e)
    finally:
        db.close()


# create_table()
# insert_data()
# update_data()
delete_date()

if __name__ == '__main__':
    print()
